package batch.pmgm;

import java.util.*;
import java.sql.*;

import java.util.Date;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.*;

import batch.common.db.*;
import batch.common.report.*;

public class Pmgm_status {
    private final String TODAY  = DateFormatUtils.format(new Date(), "yyyyMMdd");

    private final String MAILSUBJECT = "PmgmSt_status Error! (" + TODAY + ")";

    private String batch_date;

    /*******************************************************************************************************/
    /* Name : Pmgm_status
    /* Arguments : none
    /* Return : none
    /* Description : constructor
    /*******************************************************************************************************/
    public Pmgm_status(){
        batch_date = TODAY;
    }

    /*******************************************************************************************************/
    /* Name : Pmgm_status
    /* Arguments : String(--yyyymmdd--)
    /* Return : none
    /* Description : constructor
    /*******************************************************************************************************/
    public Pmgm_status(String date){
        batch_date = date;
    }

    /*******************************************************************************************************/
    /* Name : main
    /* Arguments : String(--yyyymmdd--)
    /*******************************************************************************************************/
    public static void main(String[] args) {
        Pmgm_status pmgm_status = null;

        if ( args == null || args.length == 0 ) {
            pmgm_status = new Pmgm_status();
        }
        else {
            pmgm_status = new Pmgm_status(args[0]);
        }

        Logger.getLogger("pmgm.pmgm_status").info(" ( * ) Start.");
        pmgm_status.start();
        Logger.getLogger("pmgm.pmgm_status").info(" ( * ) End.");
    }

    /*******************************************************************************************************/
    /* Name : start
    /* Arguments : none
    /* Return : none
    /* Description : update status
    /*******************************************************************************************************/
    public void start() {
        Connection conn = null;

        try {
            // get connection
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);

            Logger.getLogger("pmgm.pmgm_status").info(" ( * ) update promotion status into 'Ongoing'.");
            // get promotion list to be update into 'Ongoing'.
            Vector vPromo_list = selectOngoingTarget(conn);
            // update promotion status into 'Ongoing'
            if (vPromo_list.size() > 0) {
                updateToOngoing(vPromo_list, conn);
            }
            else {
                Logger.getLogger("pmgm.pmgm_status").info(" ( * ) there is no target promotion.");
            }

            Logger.getLogger("pmgm.pmgm_status").info(" ( * ) update promotion status into 'Closed'.");
            // get promotion list to be update into 'Closed'.
            vPromo_list = selectClosedTarget(conn);
            // update promotion status into 'Closed'
            if (vPromo_list.size() > 0) {
                updateToClosed(vPromo_list, conn);
            }
            else {
                Logger.getLogger("pmgm.pmgm_status").info(" ( * ) there is no target promotion.");
            }

            conn.commit();
        } catch(Exception e) {
            if (conn != null) try { conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); }

            e.printStackTrace();

            Logger.getLogger("pmgm.pmgm_status").info(" ( ! ) " + e.toString());

            StackTraceElement[] stElement = e.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("pmgm.pmgm_status").info(" ( ! ) " + stElement[p].toString());
            }

            ReportError.byEmail(MAILSUBJECT, e.toString());
        } finally {
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }

    /*******************************************************************************************************/
    /* Name : selectOngoingTarget
    /* Arguments : Connection
    /* Return : Vector vPromo_list (--table name array--)
    /* Description : select promotion list to be updated into 'Ongoing'.
    /*******************************************************************************************************/
    public Vector selectOngoingTarget(Connection conn) throws Exception {
        Vector vPromo_list = new Vector();
        String sql = "SELECT MST_CD, TITLE_NM FROM G_PMGM_MST WHERE STATUS_CD='P' AND DELETE_CD='N' AND START_YMD<='" + batch_date + "'";

        Statement stmt = null;
        ResultSet rs = null;

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                String[] arrList = new String[2];
                arrList[0] = rs.getString(1);
                arrList[1] = rs.getString(2);
                vPromo_list.addElement(arrList);
            }
        }
        finally {
            if (rs != null) try { rs.close(); } catch (Exception e) {}
            if (stmt != null) try { stmt.close(); } catch (Exception e) {}
        }

        return vPromo_list;
    }

    /*******************************************************************************************************/
    /* Name : updateToOngoing
    /* Arguments : Vector, Connection
    /* Return : void
    /* Description : update promotion status to Ongoing.
    /*******************************************************************************************************/
    public void updateToOngoing(Vector vPromo_list, Connection conn) throws Exception {
        String sUpdateSQL = "UPDATE G_PMGM_MST SET STATUS_CD='O', UPDATE_YMDHMS=to_char(sysdate,'yyyymmddhh24miss') WHERE MST_CD=?";
        PreparedStatement pstmtUpdate = null;
        int nCnt = 0;

        try {
            pstmtUpdate = conn.prepareStatement(sUpdateSQL);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmtUpdate.setString(1, arrList[0]);
                nCnt = pstmtUpdate.executeUpdate();
                if (nCnt==1) {
                    Logger.getLogger("pmgm.pmgm_status").info(" ( * ) " + arrList[1] + "[" + arrList[0] + "] updated.");
                }
                else {
                    throw new Exception("Rows over one are updated");
                }
            }
            Logger.getLogger("pmgm.pmgm_status").info("");
        }
        finally {
            if (pstmtUpdate!=null) try { pstmtUpdate.close(); } catch (Exception e) {}
        }
    }

    /*******************************************************************************************************/
    /* Name : selectClosedTarget
    /* Arguments : Connection
    /* Return : Vector vPromo_list (--table name array--)
    /* Description : select promotion list to be updated into 'Closed'.
    /*******************************************************************************************************/
    public Vector selectClosedTarget(Connection conn) throws Exception {
        Vector vPromo_list = new Vector();
        String sql = "SELECT MST_CD, TITLE_NM FROM G_PMGM_MST WHERE STATUS_CD='O' AND DELETE_CD='N' AND END_YMD<=TO_CHAR(ADD_MONTHS(TO_DATE('" + batch_date + "','YYYYMMDD'), -1), 'YYYYMMDD')";

        Statement stmt = null;
        ResultSet rs = null;

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                String[] arrList = new String[2];
                arrList[0] = rs.getString(1);
                arrList[1] = rs.getString(2);
                vPromo_list.addElement(arrList);
            }
        }
        finally {
            if (rs != null) try { rs.close(); } catch (Exception e) {}
            if (stmt != null) try { stmt.close(); } catch (Exception e) {}
        }

        return vPromo_list;
    }

    /*******************************************************************************************************/
    /* Name : updateToClosed
    /* Arguments : Vector, Connection
    /* Return : void
    /* Description : update promotion status to Ongoing.
    /*******************************************************************************************************/
    public void updateToClosed(Vector vPromo_list, Connection conn) throws Exception {
        String sUpdateSQL = "UPDATE G_PMGM_MST SET STATUS_CD='C', UPDATE_YMDHMS=to_char(sysdate,'yyyymmddhh24miss') WHERE MST_CD=?";
        PreparedStatement pstmtUpdate = null;
        int nCnt = 0;

        try {
            pstmtUpdate = conn.prepareStatement(sUpdateSQL);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmtUpdate.setString(1, arrList[0]);
                nCnt = pstmtUpdate.executeUpdate();
                if (nCnt==1) {
                    Logger.getLogger("pmgm.pmgm_status").info(" ( * ) " + arrList[1] + "[" + arrList[0] + "] updated.\n");
                }
                else {
                    throw new Exception("Rows over one are updated");
                }
            }
            Logger.getLogger("pmgm.pmgm_status").info("");
        }
        finally {
            if (pstmtUpdate!=null) try { pstmtUpdate.close(); } catch (Exception e) {}
        }
    }
}
